Attribute VB_Name = "xxxy_charts"
Option Explicit

'88888888888888888888888888888888888888888888888888888888888888888888888888888888
'
'   create an empty chart
'
'88888888888888888888888888888888888888888888888888888888888888888888888888888888
Private Function create_empty_chart() As Chart
    Dim orig_selection As Worksheet
    Set orig_selection = ActiveSheet
    
    Dim chtChart As Chart
    Set chtChart = ActiveWorkbook.Charts.Add(Before:=Worksheets(1))
    With chtChart
        .ChartType = xlXYScatterLinesNoMarkers
        Dim my_series As Series
        For Each my_series In .SeriesCollection
            my_series.Delete
        Next my_series
    End With
    Set create_empty_chart = chtChart

    orig_selection.Select
End Function




'88888888888888888888888888888888888888888888888888888888888888888888888888888888
'
'   Get all selected columns and column headers
'
'88888888888888888888888888888888888888888888888888888888888888888888888888888888
Private Sub get_selected_data(data As Collection, names As Collection, cnt_rows As Integer)
    ' get selection
    Dim rngDataSource As Range
    Set rngDataSource = Application.Selection
    
    ' get row counts
    cnt_rows = rngDataSource.Rows.Count
    Dim subrange As Range
    For Each subrange In rngDataSource.Areas
        Dim col As Integer
        col = subrange.Columns.Count
        For col = 1 To subrange.Columns.Count
            names.Add (subrange.Cells(1, col))
            data.Add (subrange.Cells(2, col).Resize(cnt_rows - 1, 1))
        Next col
    Next subrange
End Sub





'88888888888888888888888888888888888888888888888888888888888888888888888888888888
'
'   Get all selected data in X X Y format
'
'88888888888888888888888888888888888888888888888888888888888888888888888888888888
Private Sub get_selected_data_XXY(datax As Collection, datay As Range, namesx As Collection, namey As Range, cnt_rows As Integer)
        
    ' forward to real function which does all the work
    get_selected_data datax, namesx, cnt_rows
    
    ' transfer last name and last column into y
    Set datay = datax.Item(datax.Count)
    datax.Remove datax.Count
    Set namey = namesx.Item(namesx.Count)
    namesx.Remove namesx.Count
End Sub




'88888888888888888888888888888888888888888888888888888888888888888888888888888888
'
'   Get all selected data in interleaved X Y X Y format
'
'88888888888888888888888888888888888888888888888888888888888888888888888888888888
Private Sub get_selected_data_XYXY(datax As Collection, datay As Collection, namesx As Collection, namesy As Collection, cnt_rows As Integer)
        
    ' forward to real function which does all the work
    Dim data As New Collection
    Dim names As New Collection
    get_selected_data data, names, cnt_rows
    
    ' transfer alternate names and columns into x and y
    Dim i As Integer
    For i = 1 To data.Count Step 2
        datax.Add (data(i))
        datay.Add (data(i + 1))
        namesx.Add (names(i))
        namesy.Add (names(i + 1))
    Next i
End Sub



'88888888888888888888888888888888888888888888888888888888888888888888888888888888
'
'   add_XXY_to_chart
'
'       add data in X X Y format in chart
'
'88888888888888888888888888888888888888888888888888888888888888888888888888888888
Private Sub add_XXY_to_chart(dest_chart As Chart, datax As Collection, datay As Range, namesx As Collection, cnt_rows As Integer)
    Dim x As Integer
    For x = 1 To datax.Count
        Dim new_series As Series
        Set new_series = dest_chart.SeriesCollection.NewSeries
        With new_series
            .Values = datay
            .XValues = datax.Item(x)
            .Border.Weight = xlMedium
            ' set name as a dynamic formula
            .name = "XXXXX"
            Dim pos As Integer
            pos = InStr(.Formula, """XXXXX""")
            .Formula = Left(.Formula, pos - 1) & namesx.Item(x).Worksheet.name & "!" & namesx.Item(x).Address & Mid(.Formula, pos + 7)
        End With
    Next x

End Sub




'88888888888888888888888888888888888888888888888888888888888888888888888888888888
'
'   add_XYXY_to_chart
'
'       add data in interleaved X Y X Y format in chart
'
'88888888888888888888888888888888888888888888888888888888888888888888888888888888
Private Sub add_XYXY_to_chart(dest_chart As Chart, datax As Collection, datay As Collection, namesx As Collection, cnt_rows As Integer)
    Dim x As Integer
    For x = 1 To datax.Count
        Dim new_series As Series
        Set new_series = dest_chart.SeriesCollection.NewSeries
        With new_series
            .Values = datay.Item(x)
            .XValues = datax.Item(x)
            .Border.Weight = xlMedium
            ' set name as a dynamic formula
            .name = "XXXXX"
            Dim pos As Integer
            pos = InStr(.Formula, """XXXXX""")
            .Formula = Left(.Formula, pos - 1) & namesx.Item(x).Worksheet.name & "!" & namesx.Item(x).Address & Mid(.Formula, pos + 7)
        End With
    Next x

End Sub


'88888888888888888888888888888888888888888888888888888888888888888888888888888888
'
'   choose_chart
'
'       choose a current chart
'
'88888888888888888888888888888888888888888888888888888888888888888888888888888888
Private Function choose_chart() As Chart
    If Charts.Count < 1 Then
        MsgBox "This worksheet contains no charts", vbExclamation
        End
    End If
    
    d_choose.l_items.Clear
    Dim mychart As Chart
    For Each mychart In Charts
        d_choose.l_items.AddItem (mychart.name)
    Next mychart
    d_choose.l_items.ListIndex = d_choose.l_items.ListCount - 1
    d_choose.b_ok.SetFocus
    d_choose.Show
    If d_choose.l_items.Value = "" Then End
    Set choose_chart = Charts(d_choose.l_items.Value)
End Function



'88888888888888888888888888888888888888888888888888888888888888888888888888888888
'
'   create_XXY_chart
'
'       create chart with one Y and multiple X
'
'88888888888888888888888888888888888888888888888888888888888888888888888888888888
Public Sub create_XXY_chart()

    Dim datax As New Collection
    Dim datay As Range
    Dim namesx As New Collection
    Dim namey As Range
    Dim cnt_rows As Integer
    get_selected_data_XXY datax, datay, namesx, namey, cnt_rows
    
    
    Dim new_chart As Chart
    Set new_chart = create_empty_chart

    add_XXY_to_chart new_chart, datax, datay, namesx, cnt_rows
    
    
    
End Sub


'88888888888888888888888888888888888888888888888888888888888888888888888888888888
'
'   add_to_XXY_chart
'
'       add to chart with one Y and multiple X
'
'88888888888888888888888888888888888888888888888888888888888888888888888888888888
Public Sub add_to_XXY_chart()

    Dim datax As New Collection
    Dim datay As Range
    Dim namesx As New Collection
    Dim namey As Range
    Dim cnt_rows As Integer
    get_selected_data_XXY datax, datay, namesx, namey, cnt_rows
    
    Dim existing_chart As Chart
    Set existing_chart = choose_chart

    add_XXY_to_chart existing_chart, datax, datay, namesx, cnt_rows
    
    
    
End Sub






'88888888888888888888888888888888888888888888888888888888888888888888888888888888
'
'   create_XYXY_chart
'
'       create chart with X and Y interleaved
'
'88888888888888888888888888888888888888888888888888888888888888888888888888888888
Public Sub create_XYXY_chart()

    Dim datax As New Collection
    Dim datay As New Collection
    Dim namesx As New Collection
    Dim namesy As New Collection
    Dim cnt_rows As Integer
    get_selected_data_XYXY datax, datay, namesx, namesy, cnt_rows
    
    
    Dim new_chart As Chart
    Set new_chart = create_empty_chart

    add_XYXY_to_chart new_chart, datax, datay, namesx, cnt_rows
    
    
    
End Sub


'88888888888888888888888888888888888888888888888888888888888888888888888888888888
'
'   add_to_XYXY_chart
'
'       add to existing chart with X and Y interleaved
'
'88888888888888888888888888888888888888888888888888888888888888888888888888888888
Public Sub add_to_XYXY_chart()

    Dim datax As New Collection
    Dim datay As New Collection
    Dim namesx As New Collection
    Dim namesy As New Collection
    Dim cnt_rows As Integer
    get_selected_data_XYXY datax, datay, namesx, namesy, cnt_rows
    
    
    Dim existing_chart As Chart
    Set existing_chart = choose_chart

    add_XYXY_to_chart existing_chart, datax, datay, namesx, cnt_rows
    
    
    
End Sub




